package com.hung.pro.iFlashCards.dao;

import com.hung.pro.iFlashCards.bean.CardBean;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Manipulate Card from DB.
 * User: hung
 * Date: 2008/05/01
 * Time: 16:14:08
 * To change this template use File | Settings | File Templates.
 */
public class CardBeanDao extends DBDao {
    public CardBeanDao(String dbName) {
        super(dbName);
    }

    public CardBeanDao() {
        super();
    }

    /**
     * Insert card to dB
     *
     * @param card card to insert into db
     */
    public int insertCard(CardBean card) throws Exception {
        int cardId = -1;
        try {
            PreparedStatement stmInsertCard = this.getConnection().prepareStatement(INSERT_CARD, PreparedStatement.RETURN_GENERATED_KEYS);
            stmInsertCard.setInt(1, card.getLexiconId());
            stmInsertCard.setString(2, card.getWord());
            stmInsertCard.setString(3, card.getReading());
            stmInsertCard.setString(4, card.getMeaning());
            stmInsertCard.setString(5, card.getExample());
            stmInsertCard.setString(6, card.getNote());
            //insert into db
            stmInsertCard.executeUpdate();
            ResultSet rs = stmInsertCard.getGeneratedKeys();
            if (rs.next()) {
                cardId = rs.getInt(1);
            }
            return cardId;
        }
        catch (SQLException e) {
            throw e;
        }

    }

    /**
     * update existing card
     *
     * @param card card to be updated
     */
    public void updateCard(CardBean card) throws Exception {
        try {
            PreparedStatement stmUpdateCard = this.getConnection().prepareStatement(UPDATE_CARD);
            stmUpdateCard.setString(1, card.getWord());
            stmUpdateCard.setString(2, card.getReading());
            stmUpdateCard.setString(3, card.getMeaning());
            stmUpdateCard.setString(4, card.getExample());
            stmUpdateCard.setString(5, card.getNote());
            stmUpdateCard.setInt(6, card.getId());
            //update
            stmUpdateCard.executeUpdate();
        }
        catch (SQLException e) {
            throw e;
        }

    }

    /**
     * update card status
     */
    public void updateCardStatus(CardBean card) throws Exception {
        try {
            PreparedStatement stmUpdateCard = this.getConnection().prepareStatement(UPDATE_CARD_STATUS);
            stmUpdateCard.setInt(1, card.getStatus());
            stmUpdateCard.setInt(2, card.getId());
            //update
            stmUpdateCard.executeUpdate();
        }
        catch (SQLException e) {
            throw e;
        }
    }

    /**
     * delete card from DB
     *
     * @param cardId of the card to be deleted
     */
    public void deleteCardById(int cardId) throws Exception {
        try {
            PreparedStatement stmDeleteCard = this.getConnection().prepareStatement(DELTE_CARD);
            stmDeleteCard.setInt(1, cardId);
            stmDeleteCard.executeUpdate();
        }
        catch (SQLException e) {
            throw e;
        }
    }

    public List<CardBean> getCardsBelongingToLexicon(int lexiconId) throws Exception {
        List<CardBean> cardList = new ArrayList<CardBean>();
        try {
            PreparedStatement stmSelectCards = this.getConnection().prepareStatement(SELECT_CARD);
            stmSelectCards.setInt(1, lexiconId);
            ResultSet resultSet = stmSelectCards.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt(CardBean.COLUMN_ID);
                String word = resultSet.getString(CardBean.COLUMN_WORRD);

                String reading = resultSet.getString(CardBean.COLUMN_READING);
                String meaning = resultSet.getString(CardBean.COLUMN_MEANING);
                String example = resultSet.getString(CardBean.COLUMN_EXAMPLE);
                String note = resultSet.getString(CardBean.COLUMN_NOTE);
                //new Card instance

                CardBean card = new CardBean(id,
                        lexiconId,
                        word,
                        meaning,
                        example,
                        reading,
                        note);
                cardList.add(card);
            }
        } catch (SQLException e) {
            throw e;
        }
        return cardList;
    }

    public List<CardBean> getCardsBelongingToLexiconWithStatus(int lexiconId, int status) throws Exception {
        List<CardBean> cardList = new ArrayList<CardBean>();
        try {
            PreparedStatement stmSelectCards = this.getConnection().prepareStatement(SELECT_CARD_BY_STATUS);
            stmSelectCards.setInt(1, lexiconId);
            stmSelectCards.setInt(2, status);
            ResultSet resultSet = stmSelectCards.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt(CardBean.COLUMN_ID);
                String word = resultSet.getString(CardBean.COLUMN_WORRD);

                String reading = resultSet.getString(CardBean.COLUMN_READING);
                String meaning = resultSet.getString(CardBean.COLUMN_MEANING);
                String example = resultSet.getString(CardBean.COLUMN_EXAMPLE);
                String note = resultSet.getString(CardBean.COLUMN_NOTE);
                //new Card instance

                CardBean card = new CardBean(id,
                        lexiconId,
                        word,
                        meaning,
                        example,
                        reading,
                        note);
                cardList.add(card);
            }
        } catch (SQLException e) {
            throw e;
        }
        return cardList;
    }

    /****************** static SQL ***************/
    /**
     * Insert card SQL
     */
    private static final String INSERT_CARD = "" +
            "INSERT INTO CARD(          " +
            "LEXICON,                   " +
            "WORD,                      " +
            "READING,                   " +
            "MEANING,                   " +
            "EXAMPLE,                   " +
            "NOTE,                       " +
            "STATUS                      " +
            ")                          " +
            "VALUES(?, ?, ?, ?, ?, ?, 0 )";

    /**
     * update Card SQL
     */
    private static final String UPDATE_CARD = "" +
            "UPDATE CARD SET        " +
            "WORD = ?,              " +
            "READING = ?,           " +
            "MEANING = ?,           " +
            "EXAMPLE = ?,           " +
            "NOTE = ?               " +
            "WHERE ID = ?";
    /**
     * update card status
     */
    private static final String UPDATE_CARD_STATUS = "" +
            "UPDATE CARD SET STATUS = ? WHERE ID = ?";
    /**
     * delete Card
     */
    private static final String DELTE_CARD = "" +
            "DELETE FROM CARD WHERE ID = ?";

    /**
     * select card
     */
    private static final String SELECT_CARD = "" +
            "SELECT * FROM CARD WHERE LEXICON = ?";

    private static final String SELECT_CARD_BY_STATUS = "" +
            "SELECT * FROM CARD WHERE LEXICON = ? AND STATUS = ?";


}
